import xlwings
import sys
import os
import time
import datetime as dt
import calendar
from dateutil.relativedelta import relativedelta

# 初始化程序
sys.setrecursionlimit(10000)  # 递归上线
app = xlwings.App(visible=False, add_book=False)  # 创建应用


# 函数库
# 创建Book
def add_book():
    book = app.books.add()
    return book


# 读取Book
def read_book(book_address):
    book = app.books.open(f"{book_address}")
    return book


# 创建Sheet
def add_sheet(work_book, sheet_name, after_name):
    sheet = work_book.sheets.add(f"{sheet_name}", after=after_name)
    return sheet


# 读取Sheet
def read_sheet(work_book, sheet_address):
    sheet = work_book.sheets[sheet_address]
    return sheet


# 写入Sheet
def write_sheet(sheet, sheet_data):
    sheet.range("a1").value = sheet_data


# 获取总行
def get_row_max(sheet):
    info = sheet.used_range  # 找最后行
    row_max = info.last_cell.row
    return row_max


# 切片时转换标准时
def scale_to_time(time_scale):
    seconds = round(time_scale * 24 * 60 * 60, 2)  # 换算总时间(秒)
    m, s = divmod(seconds, 60)  # 计算总分钟(余秒)
    h, m = divmod(m, 60)  # 换算总小时(余分)
    time_str = f'{int(h)}:{int(m)}:{int(s)}'  # 获取打卡时刻
    return time_str


# 标准时转换切片时
def time_to_scale(time_time):
    seconds = round(time_time.hour * 3600 + time_time.minute * 60 + time_time.second)
    time_scale = seconds / (24 * 60 * 60)
    return time_scale


# 初始化原始数据
book_name = 'test.xlsx'  # 原始数据表文件名
original_address = book_name  # 原始数据地址
original_book = read_book(original_address)  # 实例原始数据簿
original_sheet = original_book.sheets[0]  # 实例原始数据表
sheet_row_max = get_row_max(original_sheet)  # 获取原始数据表最大行
print('数据表总行数', sheet_row_max)  # 确认数据表总行数
original_array = original_sheet.range(f"a2:r{sheet_row_max + 1}").value  # 获取原始数据表数据（尾部附加1行空值）
print('数据总行数', len(original_array))  # 确认数据总行数
print('空行：', original_array[sheet_row_max - 1])  # 确认空值是否存在

# 格式化日期与时间
for i in range(sheet_row_max - 1):
    # 格式化日期
    original_date = original_array[i][10]  # 获取日期(int)
    date_str = original_date.replace('\t', '')  # 删除 tab
    original_array[i][10] = dt.datetime.strptime(date_str, '%Y-%m-%d')  # 转换日期类型(datetime.datetime)


class Attendance:
    def __init__(self):
        self.first_line_is_in = False  # bool
        self.is_in = False  # bool
        self.in_time = None  # datetime
        self.out_time = None  # datetime
        self.am_work_on = None  # str
        self.am_work_off = '上午下班打卡异常'  # str
        self.pm_work_on = None  # str
        self.pm_work_off = '下午下班打卡异常'  # str
        self.work_state = '员工在岗正常'  # str

    def str(self):
        attendance_str = f'{self.am_work_on};{self.am_work_off};{self.pm_work_on};{self.pm_work_off};{self.work_state}'
        return attendance_str


# 员工信息
class Person:
    def __init__(self, row):
        self.ID = original_array[row][1]  # int
        self.department = original_array[row][12]  # str
        self.name = original_array[row][0]  # str
        self.times = dt.datetime.strptime(f'{original_array[row][10].date()} {scale_to_time(original_array[row][11])}',
                                          '%Y-%m-%d %H:%M:%S')  # datetime
        self.orientation = original_array[row][14]  # str


class PersonTimeSlot:
    def __init__(self, row, in_time, out_time):
        self.ID = original_array[row][1]  # int
        self.department = original_array[row][12]  # str
        self.name = original_array[row][0]  # str
        self.in_time = in_time  # datetime
        self.out_time = out_time  # datetime


# 汇总在岗时间
def sum_times():
    # 初始化
    row = 0  # 实例行数
    times = 0  # 实例在岗总时长
    sum_data = [['姓名', '时长(小时)']]  # 实例时间汇总表数据

    while True:
        # 检验数据表是否缺失
        if os.path.isfile(f'{original_address}') is False:
            print(f'时间数据表：{original_address} 缺失')
            time.sleep(5)
            original_book.save()
            original_book.close()
            exit()

        name = original_array[row][0]  # 获取员工姓名
        orientation = original_array[row][14]  # 确认进出门方向

        # 结束判定
        if name is None:
            print('在岗时间汇总完成')
            sum_book = add_book()  # 实例在岗时间汇总数据簿
            sum_sheet = sum_book.sheets[0]  # 实例在岗时间汇总数据表
            sum_sheet.name = '在岗时间汇总表'  # 数据表重命名
            write_sheet(sum_sheet, sum_data)  # 写入数据
            sum_book.save('在岗时间汇总表.xlsx')
            sum_book.close()
            original_book.save()
            return

        # 确认个人信息,记录总时间
        if name != original_array[row + 1][0]:
            hours = round(times / 60, 2)  # 分钟换算小时，保留两位小数
            original_sheet.range(f"s{row + 2}").value = [name, hours]  # 写入数据
            sum_data.append([name, hours])
            row += 1
            times = 0

        else:
            # 个人时长叠加
            if orientation == '出':
                times += original_array[row][17]  # 在岗时间累加
            row += 1


# 检查考勤状态
def check_work_attendance():
    # 检验数据表是否缺失
    if os.path.isfile(f'{original_address}') is False:
        print(f'时间数据表：{original_address} 缺失')
        time.sleep(5)
        original_book.save()
        original_book.close()
        exit()

    # 创建'考勤汇总'工作簿
    attendance_book = app.books.add()  # 创建'考勤汇总'工作簿
    attendance_sheet = attendance_book.sheets.add('考勤汇总表')  # 创建'考勤汇总'工作表
    attendance_array = []  # 创建'考勤汇总'数据列表

    # 获取月份与日数
    last_date = dt.date.today() - relativedelta(months=2)  # 获取现实日期 date
    last_mouth_days = calendar.monthrange(last_date.year, last_date.month)[1]  # 获取上月天数 int

    # 格式化标题栏
    title_array = ['人员编号', '单位', '姓名']  # 创建标题栏
    for _ in range(last_mouth_days):
        title_array.append(dt.datetime.strptime(f'{last_date.year}-{last_date.month}-{_ + 1}', '%Y-%m-%d'))  # datetime
    attendance_array.append(title_array)  # '考勤汇总'数据列表获取标题栏
    # 实例每日打卡信息
    attendance_daily = Attendance()
    # 实例last_person空
    attendance_daily_array = []

    row = 0  # 数据行数
    day = 1
    day_time = title_array[day + 2]  # datetime
    last_time_slot = None

    while True:

        # 结束判定
        if row == sheet_row_max - 1:
            original_book.save()
            original_book.close()
            attendance_sheet.range('a1').value = attendance_array
            attendance_book.save('员工考勤汇总表.xlsx')
            attendance_book.close()
            print('员工打卡处理完成')
            print(attendance_array)
            time.sleep(3)
            break

        #  导入两行打卡数据
        new_person = Person(row)
        next_person = Person(row + 1)
        last_time_slot = None

        # 首行数据不为'进'数据无效
        if attendance_daily.first_line_is_in is False:
            if new_person.orientation == '进':
                attendance_daily.first_line_is_in = True
            elif new_person.orientation == '出':
                continue

        # 短时间打相同方向卡数据无效
        if new_person.ID == next_person.ID and \
                new_person.name == next_person.name and \
                new_person.department == next_person.department and \
                new_person.times.date() == next_person.times.date() and \
                new_person.orientation == next_person.orientation:
            row += 1
            continue

        # 遍历日期
        if day_time <= title_array[last_mouth_days + 2]:
            # 时间节点
            # 设定上午上班最早打卡时间 datetime
            am_earliest_on_time = dt.datetime.strptime(f'{day_time.date()} 4:00:00', '%Y-%m-%d %H:%M:%S')
            # 设定上午下班最早打卡时间 datetime
            am_earliest_off_time = dt.datetime.strptime(f'{day_time.date()} 11:57:00', '%Y-%m-%d %H:%M:%S')
            # 设定上午迟到打卡时间 datetime
            am_late_on_time = dt.datetime.strptime(f'{day_time.date()} 8:04:00', '%Y-%m-%d %H:%M:%S')
            # 设定上午上班旷工打卡时间 datetime
            am_absenteeism_on_time = dt.datetime.strptime(f'{day_time.date()} 8:34:00', '%Y-%m-%d %H:%M:%S')
            # 设定下午下班最早打卡时间 datetime
            pm_earliest_off_time = dt.datetime.strptime(f'{day_time.date()} 17:27:00', '%Y-%m-%d %H:%M:%S')
            # 设定下午迟到打卡时间 datetime
            pm_late_on_time = dt.datetime.strptime(f'{day_time.date()} 14:04:00', '%Y-%m-%d %H:%M:%S')
            # 设定下午上班旷工打卡时间 datetime
            pm_absenteeism_on_time = dt.datetime.strptime(f'{day_time.date()} 14:34:00', '%Y-%m-%d %H:%M:%S')
            # 设定最长外出时间 timedelta
            over_time = dt.timedelta(hours=1)

            # 同员工同日期数据
            if new_person.ID == next_person.ID and new_person.times.date() == next_person.times.date():
                #  首行打进入卡数据
                if attendance_daily.in_time is None and new_person.orientation == '出':
                    print(row, '-首行为出,无效数据')
                    row += 1
                    continue
                # 打进入卡
                if new_person.orientation == '进':
                    attendance_daily.in_time = new_person.times
                    attendance_daily.first_line_is_in = True
                    print(row, '-为进,数据已储存')
                    row += 1
                    continue

                # 打外出卡
                if new_person.orientation == '出':
                    attendance_daily.out_time = new_person.times
                    print(row, '-为出,数据已储存')
                    new_time_slot = PersonTimeSlot(row, attendance_daily.in_time, attendance_daily.out_time)
                    # 无当日打卡记录
                    if new_time_slot.in_time.date() > day_time.date():
                        attendance_daily.am_work_on = '上午旷工'
                        attendance_daily.am_work_off = '上午下班打卡异常'
                        attendance_daily.pm_work_on = '下午旷工'
                        attendance_daily.pm_work_off = '下午下班打卡异常'
                        attendance_daily.work_state = '外出超时'
                        attendance_daily_array.append(attendance_daily.str())
                        print(f'{day_time.date()}无打卡记录')
                        day += 1
                        continue
                    # 时间比较
                    # 上午上班打卡
                    if attendance_daily.am_work_on is None:
                        # 最早打卡进入时间早于最早打卡时间,打卡无效
                        if new_time_slot.in_time < am_earliest_on_time:
                            print('无效数据')
                            pass
                        # 最早打卡进入时间晚于最早打卡时间且早于最迟打卡时间
                        if am_earliest_on_time < new_time_slot.in_time < am_late_on_time:
                            attendance_daily.am_work_on = '上午上班打卡正常'
                            print(attendance_daily.am_work_on)
                        # 最早打卡进入时间晚于最迟打卡时间且早于旷工打卡时间
                        if am_late_on_time < new_time_slot.in_time < am_absenteeism_on_time:
                            attendance_daily.am_work_on = '上午上班打卡迟到'
                            print(attendance_daily.am_work_on)
                        # 最早打卡进入时间晚于上午旷工打卡时间且早于上午最早下班打卡时间
                        if am_absenteeism_on_time < new_time_slot.in_time:
                            attendance_daily.am_work_on = '上午旷工'
                            print(attendance_daily.am_work_on)

                    # 下午上班打卡
                    if attendance_daily.pm_work_on is None:
                        if new_time_slot.in_time < am_earliest_off_time:
                            pass
                        # 下午正常打卡或中午未出工作区域
                        if am_earliest_off_time < new_time_slot.in_time < pm_late_on_time or \
                                new_time_slot.in_time < am_earliest_off_time < pm_late_on_time < new_time_slot.out_time:
                            attendance_daily.am_work_on = '下午上班打卡正常'
                            print(attendance_daily.pm_work_on)
                        # 下午迟到打卡
                        if pm_late_on_time < new_time_slot.in_time < pm_absenteeism_on_time:
                            attendance_daily.pm_work_on = '下午上班打卡迟到'
                            print(attendance_daily.pm_work_on)
                        # 最早打卡进入时间晚于下午旷工打卡时间
                        if pm_absenteeism_on_time < new_time_slot.in_time:
                            attendance_daily.pm_work_on = '下午旷工'
                            print(attendance_daily.pm_work_on)

                    # 上午下班打卡
                    if attendance_daily.am_work_off != '上午下班打卡正常':
                        if new_time_slot.out_time < am_earliest_off_time:
                            attendance_daily.am_work_off = '上午提前下班'
                            print(attendance_daily.am_work_off)
                        if am_earliest_off_time < new_time_slot.out_time < pm_late_on_time or \
                                new_time_slot.in_time < am_earliest_off_time < pm_late_on_time < new_time_slot.out_time:
                            attendance_daily.am_work_off = '上午下班打卡正常'
                            print(attendance_daily.am_work_off)

                    # 下午下班打卡
                    if attendance_daily.pm_work_off != '下午下班打卡正常':
                        if pm_late_on_time < new_time_slot.out_time < pm_earliest_off_time:
                            attendance_daily.pm_work_off = '下午提前下班'
                            print(attendance_daily.pm_work_off)
                        if pm_earliest_off_time < new_time_slot.out_time:
                            attendance_daily.pm_work_off = '下午下班打卡正常'
                            print(attendance_daily.pm_work_off)

                    # 工作时外出超时
                    if last_time_slot is not None and attendance_daily.work_state == '员工在岗正常':
                        out_worktime = False
                        if am_late_on_time < new_time_slot.out_time < am_earliest_off_time or \
                                pm_late_on_time < new_time_slot.out_time < pm_earliest_off_time:
                            out_worktime = True
                        if new_time_slot.in_time - last_time_slot.out_time > over_time and \
                                out_worktime is True:
                            attendance_daily.work_state = '员工在岗异常'
                            print(attendance_daily.work_state)
                            last_time_slot = new_time_slot

                # 下组数据同人同天
                if new_person.ID == next_person.ID and new_person.times.date() == next_person.times.date():
                    row += 1
                    day += 1
                    continue

                # 下组数据同人不同天
                if new_person.ID == next_person.ID and new_person.times.date() == next_person.times.date():





            # 同员工同日期数据
            if new_person.ID == next_person.ID and new_person.times.date() < next_person.times.date():


        print(attendance_daily.str())
        break

        # break
        #                 # 最早打卡进入时间晚于最早打卡时间且早于最迟打卡时间
        #
        # # 短时间打相同方向卡数据无效
        # if new_person.ID == next_person.ID and \
        #         new_person.department == next_person.department and \
        #         new_person.name == next_person.name and \
        #         new_person.date == next_person.date and \
        #         new_person.orientation == next_person.orientation:
        #     row += 1  # 行数+1
        #     continue  # 跳过本次循环
        #
        # # 首行数据不为'进'数据无效
        # if attendance_daily.first_line_is_in is False:
        #     if new_person.orientation == '进':
        #         attendance_daily.first_line_is_in = True
        #     elif new_person.orientation == '出':
        #         continue
        #
        # # 遍历日期
        # if day <= last_mouth_days:
        #
        #     # 打进入卡数据
        #     # 同员工
        #     if new_person.ID == next_person.ID and \
        #             new_person.department == next_person.department and \
        #             new_person.name == next_person.name:
        #
        #         # 写入员工数据
        #         new_person_array = [new_person.ID, new_person.department, new_person.name]
        #
        #         # 同日期有数据
        #         if new_person.date == title_array[day + 2] and \
        #                 new_person.orientation != next_person.orientation:
        #
        #             # 打首次进入卡
        #             if new_person.orientation == '进' and \
        #                     attendance_daily.am_work_on is None and \
        #                     attendance_daily.pm_work_on is None:
        #
        #                 # 最早打卡进入时间早于最早打卡时间,打卡无效
        #                 if new_person.time_scale < am_absenteeism_on_scale:
        #                     pass
        #                 # 最早打卡进入时间晚于最早打卡时间且早于最迟打卡时间
        #                 if am_earliest_off_scale < new_person.time_scale < am_late_on_scale:
        #                     attendance_daily.am_work_on = '上午上班打卡正常'
        #                 # 最早打卡进入时间晚于最迟打卡时间且早于旷工打卡时间
        #                 if am_late_on_scale < new_person.time_scale < am_absenteeism_on_scale:
        #                     attendance_daily.am_work_on = '上午上班打卡迟到'
        #                 # 最早打卡进入时间晚于上午旷工打卡时间且早于上午最早下班打卡时间
        #                 if am_absenteeism_on_scale < new_person.time_scale < am_earliest_off_scale:
        #                     attendance_daily.am_work_on = '上午旷工'
        #                 # 最早打卡进入时间晚于上午最迟下班打卡时间且早于下午最迟上班打卡时间
        #                 if am_earliest_off_scale < new_person.time_scale < pm_late_on_scale:
        #                     attendance_daily.am_work_on = '上午旷工'
        #                     attendance_daily.pm_work_on = '下午上班打卡正常'
        #                 # 最早打卡进入时间晚于下午最迟打卡时间且早于下午旷工打卡时间
        #                 if pm_late_on_scale < new_person.time_scale < pm_absenteeism_on_scale:
        #                     attendance_daily.am_work_on = '上午旷工'
        #                     attendance_daily.pm_work_on = '下午上班打卡迟到'
        #                 # 最早打卡进入时间晚于下午旷工打卡时间
        #                 if pm_absenteeism_on_scale < new_person.time_scale:
        #                     attendance_daily.am_work_on = '上午旷工'
        #                     attendance_daily.pm_work_on = '下午旷工'
        #
        #             # 非首次进入卡
        #             if new_person.orientation == '进' and \
        #                     attendance_daily.am_work_on is not None:
        #                 # 打卡进入时间早于最早打卡时间,打卡无效
        #                 if new_person.time_scale < am_absenteeism_on_scale:
        #                     pass
        #                 # instance2 = Type(is_first = False)
        #                 # attendance_daily.am_work_on = ins2.compare(new_person.time_scale)
        #
        #             # 打外出卡数据
        #             # 上午下班打卡异常
        #             if new_person.orientation == '出' and \
        #                     attendance_daily.am_work_off == '上午下班打卡异常':
        #                 # 打卡外出时间早于最早上午上班时间,打卡无效
        #                 if new_person.time_scale < am_earliest_off_scale:
        #                     attendance_daily.am_work_off = '上午下班打卡异常'
        #                 # 打卡外出时间介于中午休息时间之间,上午下班正常
        #                 if am_earliest_off_scale < new_person.time_scale < pm_late_on_scale:
        #                     attendance_daily.am_work_off = '上午下班打卡正常'
        #             #  下午下班打卡异常
        #             if new_person.orientation == '出' and \
        #                     attendance_daily.pm_work_off == '下午下班打卡异常':
        #                 # 打卡外出时间介于下午上班时间,打卡无效
        #                 if pm_late_on_scale < new_person.time_scale < pm_earliest_off_scale:
        #                     attendance_daily.am_work_off = '上午下班打卡异常'
        #                     attendance_daily.pm_work_off = '下午下班打卡正常'
        #                 # 打卡外出时间晚于下午下班时间
        #                 if pm_earliest_off_scale < new_person.time_scale:
        #                     attendance_daily.am_work_off = '上午下班打卡异常'
        #                     attendance_daily.pm_work_off = '下午下班打卡正常'
        #
        #             # 外出时长数据
        #             if new_person.orientation == '进' and \
        #                     attendance_daily.am_work_on is not None:
        #                 new_time_out_scale = new_person.time_scale - last_person.time_scale
        #                 if over_time_scale < new_time_out_scale:
        #                     attendance_daily.work_state = '员工离岗超时'
        #
        #         # 同日期无数据
        #         if new_person.date != title_array[day + 2]:
        #             attendance_daily.am_work_on = '上午旷工'
        #             attendance_daily.am_work_off = '上午下班打卡异常'
        #             attendance_daily.pm_work_on = '下午旷工'
        #             attendance_daily.pm_work_off = '下午下班打卡异常'
        #             attendance_daily.work_state = '员工离岗超时'
        #
        #         # 员工每日打卡情况汇总
        #         # personnel_today_array = []  # 员工当日考勤情况
        #
        #         # personnel_today_array.append(
        #         #     attendance_daily.am_work_on + ';' + attendance_daily.am_work_off + ';' +
        #         #     attendance_daily.pm_work_on + ';' + attendance_daily.pm_work_off + ';' +
        #         #     attendance_daily.work_state)
        #         # new_person_array.append(personnel_today_array)
        #
        #         # print(personnel_today_array) # 员工每日打卡情况检查点
        #         print(new_person_array)
        #
        #     last_person = new_person  # 储存上行员工数据
        #     row += 1


check_work_attendance()
